﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Configuration;
using uMES.LeanManufacturing.ParameterDTO;
using uMES.LeanManufacturing.DBUtility;
using System.Drawing;

namespace uMES.LeanManufacturing.ReportBusiness
{
    public class uMESContainerPlatoonBusiness
    {
        #region 获取指定时间段内设备组的负载状态
        public DataTable GetResourceGroupStatus(DateTime startDate, DateTime endDate)
        {
            DataTable dtRGStatusList = new DataTable();
            dtRGStatusList.Columns.Add("RGID");
            dtRGStatusList.Columns.Add("RGName");
            dtRGStatusList.Columns.Add("RGDate", Type.GetType("System.DateTime"));
            dtRGStatusList.Columns.Add("RGStatus");

            string strStartDate = startDate.ToString("yyyy-MM-dd");
            string strEndDate = endDate.ToString("yyyy-MM-dd");

            StringBuilder strQuery = new StringBuilder();
            strQuery.AppendLine("SELECT di.resourcegroupid,rg.resourcegroupname,di.totalgs,di.plannedstartdate,di.plannedcompletiondate");
            strQuery.AppendLine("FROM dispatchinfo di");
            strQuery.AppendLine("LEFT JOIN resourcegroup rg ON rg.resourcegroupid = di.resourcegroupid");
            strQuery.AppendLine("WHERE  di.isaps = 1 AND di.dispatchtype = 0 AND di.dispatchtotype = 0");
            //strQuery.AppendLine(string.Format("AND (di.plannedstartdate BETWEEN TO_DATE('{0} 00:00:00','yyyy-MM-dd HH24:MI:SS') AND TO_DATE('{1} 23:59:59','yyyy-MM-dd HH24:MI:SS')", strStartDate, strEndDate));
            //strQuery.AppendLine(string.Format("OR di.plannedcompletiondate BETWEEN TO_DATE('{0} 00:00:00','yyyy-MM-dd HH24:MI:SS') AND TO_DATE('{1} 23:59:59','yyyy-MM-dd HH24:MI:SS'))", strStartDate, strEndDate));
            strQuery.AppendLine(string.Format("AND di.plannedstartdate <= TO_DATE('{0} 23:59:59','yyyy-MM-dd HH24:MI:SS')", strEndDate));
            strQuery.AppendLine(string.Format("AND di.plannedcompletiondate > TO_DATE('{0} 00:00:00','yyyy-MM-dd HH24:MI:SS')", strStartDate));
            strQuery.AppendLine("AND di.plannedstartdate IS NOT NULL AND di.plannedcompletiondate IS NOT NULL");

            DataTable dtDispatchList = OracleHelper.GetDataTable(strQuery.ToString());

            string strDayGS = ConfigurationManager.AppSettings["DayGS"];

            strQuery = new StringBuilder();
            strQuery.AppendLine("SELECT di.resourcegroupid,rg.resourcegroupname,");
            strQuery.AppendLine("((SELECT COUNT(*) FROM resourcegroupentries re WHERE re.resourcegroupid = di.resourcegroupid) * " + strDayGS + " * 60) AS stdgs");
            strQuery.AppendLine("FROM dispatchinfo di");
            strQuery.AppendLine("LEFT JOIN resourcegroup rg ON rg.resourcegroupid = di.resourcegroupid");
            strQuery.AppendLine("WHERE  di.isaps = 1 AND di.dispatchtype = 0 AND di.dispatchtotype = 0");
            //strQuery.AppendLine(string.Format("AND (di.plannedstartdate BETWEEN TO_DATE('{0} 00:00:00','yyyy-MM-dd HH24:MI:SS') AND TO_DATE('{1} 23:59:59','yyyy-MM-dd HH24:MI:SS')", strStartDate, strEndDate));
            //strQuery.AppendLine(string.Format("OR di.plannedcompletiondate BETWEEN TO_DATE('{0} 00:00:00','yyyy-MM-dd HH24:MI:SS') AND TO_DATE('{1} 23:59:59','yyyy-MM-dd HH24:MI:SS'))", strStartDate, strEndDate));
            strQuery.AppendLine(string.Format("AND di.plannedstartdate <= TO_DATE('{0} 23:59:59','yyyy-MM-dd HH24:MI:SS')", strEndDate));
            strQuery.AppendLine(string.Format("AND di.plannedcompletiondate > TO_DATE('{0} 00:00:00','yyyy-MM-dd HH24:MI:SS')", strStartDate));
            strQuery.AppendLine("AND di.resourcegroupid IS NOT NULL");
            strQuery.AppendLine("GROUP BY di.resourcegroupid,rg.resourcegroupname");

            DataTable dtRGList = OracleHelper.GetDataTable(strQuery.ToString());

            startDate = DateTime.MaxValue;

            foreach (DataRow row in dtDispatchList.Rows)
            {
                if (row["PlannedStartDate"].ToString() != string.Empty)
                {
                    if (Convert.ToDateTime(row["PlannedStartDate"].ToString()) < startDate)
                    {
                        startDate = Convert.ToDateTime(row["PlannedStartDate"].ToString());
                    }
                }
            }

            endDate = DateTime.MinValue;

            foreach (DataRow row in dtDispatchList.Rows)
            {
                if (row["PlannedCompletionDate"].ToString() != string.Empty)
                {
                    if (Convert.ToDateTime(row["PlannedCompletionDate"].ToString()) > endDate)
                    {
                        endDate = Convert.ToDateTime(row["PlannedCompletionDate"].ToString());
                    }
                }
            }

            TimeSpan daystmp = endDate - startDate;
            int dayLenth = daystmp.Days + 1;

            for (int i = 0; i < dayLenth; i++)
            {
                string strDate = startDate.AddDays(i).ToString("yyyy-MM-dd");
                DateTime tempStart = Convert.ToDateTime(strDate + " 00:00:00");
                DateTime tempEnd = Convert.ToDateTime(strDate + " 23:59:59");

                for (int k = 0; k < dtRGList.Rows.Count; k++)
                {
                    string strRGID = dtRGList.Rows[k]["ResourceGroupID"].ToString();
                    string strRGName = dtRGList.Rows[k]["ResourceGroupName"].ToString();

                    StringBuilder strFilter = new StringBuilder();
                    strFilter.Append(string.Format("ResourceGroupID = '{0}' AND (", strRGID));
                    //strFilter.Append(string.Format("PlannedStartDate BETWEEN TO_DATE('{0} 00:00:00','yyyy-MM-dd HH24:MI:SS') AND TO_DATE('{0} 23:59:59','yyyy-MM-dd HH24:MI:SS')", strDate));
                    strFilter.Append(string.Format("(PlannedStartDate >= '{0} 00:00:00' AND PlannedStartDate <= '{0} 23:59:59')", strDate));
                    strFilter.Append(" OR ");
                    //strFilter.Append(string.Format("PlannedCompletionDate BETWEEN TO_DATE('{0} 00:00:00','yyyy-MM-dd HH24:MI:SS') AND TO_DATE('{0} 23:59:59','yyyy-MM-dd HH24:MI:SS'))", strDate));
                    strFilter.Append(string.Format("(PlannedCompletionDate >= '{0} 00:00:00' AND PlannedCompletionDate <= '{0} 23:59:59'))", strDate));

                    DataRow[] rows = dtDispatchList.Select(strFilter.ToString());

                    int intTotal = 0;
                    for (int j = 0; j < rows.Length; j++)
                    {
                        DateTime dateStart = Convert.ToDateTime(rows[j]["PlannedStartDate"].ToString());
                        DateTime dateEnd = Convert.ToDateTime(rows[j]["PlannedCompletionDate"].ToString());

                        if (dateStart >= tempStart && dateEnd <= tempEnd)
                        {
                            //整个任务包含在今天之内
                            string strTotalGS = rows[j]["TotalGS"].ToString();
                            int intTaskGS = Convert.ToInt32(Convert.ToDouble(strTotalGS));
                            intTotal += intTaskGS;
                        }
                        else if (dateStart >= tempStart && dateEnd > tempEnd)
                        {
                            //任务在今天开始
                            intTotal += GetStdM(dateStart, tempEnd);
                        }
                        else if (dateStart < tempStart && dateEnd <= tempEnd)
                        {
                            //任务在今天结束
                            intTotal += GetStdM(tempStart, dateEnd);
                        }
                        else if (dateStart < tempStart && dateEnd > tempEnd)
                        {
                            //今天包含在任务之内
                            intTotal += Convert.ToInt32(strDayGS) * 60;
                        }
                    }

                    string strStatus = "0";
                    int intStdGS = Convert.ToInt32(dtRGList.Rows[k]["stdgs"].ToString());

                    if (intTotal > intStdGS)
                    {
                        strStatus = "1";
                    }

                    DataRow row = dtRGStatusList.NewRow();
                    row["RGID"] = strRGID;
                    row["RGName"] = strRGName;
                    row["RGDate"] = strDate;
                    row["RGStatus"] = strStatus;
                    dtRGStatusList.Rows.Add(row);
                }
            }

            return dtRGStatusList;
        }
        #endregion

        #region 按每天工作时间段计算两个时间点之间的时间差（单位：分钟）
        public int GetStdM(DateTime dt1, DateTime dt2)
        {
            int intTotal = 0;

            string strDayGS = ConfigurationManager.AppSettings["DayGS"];

            if (strDayGS != "24")
            {
                DataTable dtSJD = GetDaySJD_Config();

                string strDate = dt1.ToString("yyyy-MM-dd");

                foreach (DataRow row in dtSJD.Rows)
                {
                    string strStart = row["start"].ToString();
                    string strEnd = row["end"].ToString();

                    DateTime dateStart = Convert.ToDateTime(strDate + " " + strStart);
                    DateTime dateEnd = Convert.ToDateTime(strDate + " " + strEnd);

                    if (dt1 >= dateStart && dt2 <= dateEnd)
                    {
                        intTotal += Convert.ToInt32(GetM(dt1, dt2));
                    }
                    else if (dt1 >= dateStart && dt2 > dateEnd)
                    {
                        intTotal += Convert.ToInt32(GetM(dt1, dateEnd));
                    }
                    else if (dt1 < dateStart && dt2 <= dateEnd)
                    {
                        intTotal += Convert.ToInt32(GetM(dateStart, dt2));
                    }
                    else if (dt1 < dateStart && dt2 > dateEnd)
                    {
                        intTotal += Convert.ToInt32(GetM(dateStart, dateEnd));
                    }
                }
            }
            else
            {
                intTotal += Convert.ToInt32(GetM(dt1, dt2));
            }

            return intTotal;
        }
        #endregion

        #region 计算两个时间点之间的时间差（单位：小时）（四舍五入）
        public int GetH(DateTime dt1, DateTime dt2)
        {
            TimeSpan ts = dt1.Subtract(dt2);

            Double intD = ts.Days;
            Double intH = ts.Hours;
            Double intM = ts.Minutes;

            return Convert.ToInt32(Math.Abs(intD * 24 + intH + intM / 60));
        }
        #endregion

        #region 生成随机颜色
        public Color RandColor()
        {
            Random Rand_First = new Random((int)DateTime.Now.Ticks);

            System.Threading.Thread.Sleep(Rand_First.Next(50));

            Random Rand_Sencond = new Random((int)DateTime.Now.Ticks);

            int int_Red = Rand_First.Next(256);
            int int_Green = Rand_Sencond.Next(256);
            int int_Blue = (int_Red + int_Green > 400) ? 0 : 400 - int_Red - int_Green;
            int_Blue = (int_Blue > 255) ? 255 : int_Blue;

            Color result = Color.FromArgb(int_Red, int_Green, int_Blue);

            return result;
        }
        #endregion


        #region 获取派工工序详细信息
        public DataTable GetDispatchSpecInfo(Dictionary<string,string> para)
        {
            StringBuilder strQuery = new StringBuilder();
            strQuery.AppendLine("SELECT di.containerid,sb.specname,di.totalgs,di.resourcegroupid,di.specsequence,rg.resourcegroupname,di.plannedstartdate,");
            strQuery.AppendLine("       di.plannedcompletiondate,s.unitworktime,s.setupworktime,c.qty,c.originalqty, c.qty * s.unitworktime AS worktime1,");
            strQuery.AppendLine("       c.originalqty * s.unitworktime AS worktime2,di.id,NVL(c.state,0) AS state,di.status");
            //strQuery.AppendLine("       ,(SELECT COUNT(*) FROM resourcegroupentries re WHERE re.resourcegroupid = di.resourcegroupid) AS resqty");
            strQuery.AppendLine("FROM dispatchinfo di");
            strQuery.AppendLine("LEFT JOIN Container c ON c.containerid = di.containerid");
            strQuery.AppendLine("LEFT JOIN spec s ON s.specid = di.specid");
            strQuery.AppendLine("LEFT JOIN operation o ON o.operationid = s.operationid");
            strQuery.AppendLine("LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid");
            strQuery.AppendLine("LEFT JOIN resourcegroup rg ON rg.resourcegroupid = di.resourcegroupid");
            strQuery.AppendLine("WHERE di.isaps = 1");

            if (para.ContainsKey("DispatchinfoID"))
            {
                strQuery.AppendLine("AND di.id='" + para["DispatchinfoID"] + "'");
            }

            if (para.ContainsKey("DispatchinfoName"))
            {
                strQuery.AppendLine("AND di.dispatchinfoname='" + para["DispatchinfoName"] + "'");
            }
            DataTable dt = OracleHelper.GetDataTable(strQuery.ToString());
            dt.Columns.Add("resourceQty"); //资源组下管理的设备数量
            dt.Columns.Add("productioncapacity");//资源生产总能力
            string strDayGS = ConfigurationManager.AppSettings["DayGS"].ToString();

            string strGroupIDList = string.Empty;
            for (int i =0;i<dt.Rows.Count;i++)
            {
                string strGroupID = dt.Rows[i]["resourcegroupid"].ToString();
                if (strGroupID != "")
                {
                    if (strGroupIDList.Contains(strGroupID)==false)
                    {
                        strGroupIDList+="'" + strGroupID + "',";
                    }
                }
            }
            if (strGroupIDList!=null && strGroupIDList!="")
            {
                para.Add("GroupIDList", strGroupIDList.TrimEnd(','));
            }
            DataTable dtResource = GetResourceInfo(para);

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                string strGroupID = dt.Rows[i]["resourcegroupid"].ToString();
                dt.Rows[i]["resourceQty"] = "0";
                if (dtResource.Rows.Count>0)
                {
                    if (strGroupID != "")
                    {
                        DataRow[] dr = dtResource.Select("resourcegroupid='"+strGroupID+"'");
                        dt.Rows[i]["resourceQty"] = dr.Length;

                        int intDayGS = 0;
                        if (strDayGS != null && strDayGS != "")
                        {
                            intDayGS = Convert.ToInt32(strDayGS);
                        }

                        dt.Rows[i]["productioncapacity"] = intDayGS * dr.Length;
                    }
                }
       
            }
            return dt;
        }

        public DataTable GetResourceInfo(Dictionary<string,string> para)
        {
            bool isExecute = false;
            StringBuilder sb = new StringBuilder();
            sb.Append(@"SELECT rp.*,r.resourceid,r.resourcename,r.description
                        FROM  resourcegroup rp
                        LEFT JOIN resourcegroupentries re ON re.resourcegroupid = rp.resourcegroupid
                        LEFT JOIN resourcedef r ON r.resourceid =re.entriesid
                        WHERE 1 = 1
                    ");
            if (para.ContainsKey("GroupIDList") && !string.IsNullOrEmpty(para["GroupIDList"]))             
            {
                sb.AppendLine("AND rp.resourcegroupid IN("+para["GroupIDList"] +")");
                isExecute = true;
            }
            DataTable dt = new DataTable();
            if (isExecute==true)
            {
                dt = OracleHelper.GetDataTable(sb.ToString());
            }
                
            return dt;
        }
        #endregion

        #region 下发指定批次的班组派工信息
        public Boolean UpdateDIState(List<string> listContainerID, int intState, string strNotes)
        {
            Boolean result = true;

            string strContainerIDs = string.Empty;
            for (int i = 0; i < listContainerID.Count; i++)
            {
                strContainerIDs = strContainerIDs + string.Format("'{0}'", listContainerID[i]);
            }
            strContainerIDs = strContainerIDs.Replace("''", "','");

            if (strContainerIDs == string.Empty)
            {
                strContainerIDs = "''";
            }

            ArrayList SQLStringList = new ArrayList();

            StringBuilder strSQL = new StringBuilder();
            if (intState == 1)
            {
                strSQL = new StringBuilder();
                strSQL.AppendLine("UPDATE dispatchinfo di SET state = 1");
                strSQL.AppendFormat("WHERE di.containerid IN ({0})", strContainerIDs);
                SQLStringList.Add(strSQL.ToString());
            }

            strSQL = new StringBuilder();
            strSQL.AppendLine(string.Format("UPDATE container c SET state = {0}, containercomments = '{1}'", intState, strNotes));
            strSQL.AppendFormat("WHERE c.containerid IN ({0})", strContainerIDs);
            SQLStringList.Add(strSQL.ToString());

            OracleHelper.ExecuteSqlTran(SQLStringList);

            return result;
        }
        #endregion

        #region 更新派工单的计划开始时间和计划完成时间
        public Boolean UpdateDispatchInfo(string strID, DateTime startTime, DateTime endTime)
        {
            Boolean result = true;

            StringBuilder strSQL = new StringBuilder();
            strSQL.AppendLine("UPDATE dispatchinfo di");
            strSQL.AppendLine(string.Format("SET di.plannedstartdate = TO_DATE('{0}','yyyy-MM-dd HH24:MI:SS')", startTime.ToString("yyyy-MM-dd HH:mm:ss")));
            strSQL.AppendLine(string.Format(", di.plannedcompletiondate = TO_DATE('{0}','yyyy-MM-dd HH24:MI:SS')", endTime.ToString("yyyy-MM-dd HH:mm:ss")));
            strSQL.AppendLine(string.Format("WHERE di.id = '{0}'", strID));

            OracleHelper.ExecuteSql(strSQL.ToString());

            return result;
        }
        #endregion

        #region 根据多个ContainerID获取班组派工信息列表
        /// <summary>
        /// 根据多个ContainerID获取班组派工信息列表
        /// </summary>
        /// <param name="listContainerID"></param>
        /// <returns></returns>
        public DataTable GetDispatchListByContainerID(List<string> listContainerID)
        {
            string strContainerIDs = string.Empty;
            for (int i = 0; i < listContainerID.Count; i++)
            {
                strContainerIDs = strContainerIDs + string.Format("'{0}'", listContainerID[i]);
            }
            strContainerIDs = strContainerIDs.Replace("''", "','");

            if (strContainerIDs == string.Empty)
            {
                strContainerIDs = "''";
            }

            StringBuilder strQuery = new StringBuilder();
            strQuery.AppendLine("SELECT di.id,di.containerid,di.workflowid,di.workflowstepid,di.specid,");
            strQuery.AppendLine("   di.specsequence,di.plannedstartdate,di.plannedcompletiondate");
            strQuery.AppendLine("FROM dispatchinfo di");
            strQuery.AppendFormat("WHERE di.containerid IN ({0})", strContainerIDs);

            DataTable DT = OracleHelper.GetDataTable(strQuery.ToString());
            return DT;
        }
        #endregion

        #region 修改批次对应订单的计划开始日期和计划完成日期
        public Boolean UpdateMfgOrderDate(string strContainerID, int intDays)
        {
            Boolean result = true;

            StringBuilder strSQL = new StringBuilder();
            strSQL.AppendLine("UPDATE mfgorder mo");
            strSQL.AppendLine(string.Format("SET mo.plannedstartdate = mo.plannedstartdate + {0},", intDays.ToString()));
            strSQL.AppendLine(string.Format("mo.plannedcompletiondate = mo.plannedcompletiondate + {0}", intDays.ToString()));
            strSQL.AppendLine(string.Format("WHERE mo.mfgorderid = (SELECT c.mfgorderid FROM container c WHERE c.containerid = '{0}')", strContainerID));

            OracleHelper.ExecuteSql(strSQL.ToString());

            return result;
        }
        #endregion

        #region 修改批次的计划开始日期和计划完成日期
        public Boolean UpdateContainerDate(string strContainerID, DateTime startDate, DateTime endDate)
        {
            Boolean result = true;

            StringBuilder strSQL = new StringBuilder();
            strSQL.AppendLine("UPDATE container c");
            strSQL.AppendLine(string.Format("SET c.plannedstartdate = TO_DATE('{0} 00:00:00','yyyy-MM-dd HH24:MI:SS'),", startDate.ToString("yyyy-MM-dd")));
            strSQL.AppendLine(string.Format("c.plannedcompletiondate = TO_DATE('{0} 00:00:00','yyyy-MM-dd HH24:MI:SS')", endDate.ToString("yyyy-MM-dd")));
            strSQL.AppendLine(string.Format("WHERE c.containerid = '{0}'", strContainerID));

            OracleHelper.ExecuteSql(strSQL.ToString());

            return result;
        }
        #endregion

        #region 修改批次是否排产标记
        public Boolean UpdateContainerIsAPS(string strContainerID)
        {
            Boolean result = true;

            StringBuilder strSQL = new StringBuilder();
            strSQL.AppendLine("UPDATE container c SET isaps = 1");
            strSQL.AppendLine(string.Format("WHERE c.containerid = '{0}'", strContainerID));

            OracleHelper.ExecuteSql(strSQL.ToString());

            return result;
        }
        #endregion

        #region 获取指定时间段内排产信息
        public uMESPagingDataDTO GetAPSMainInfo(Dictionary<string, string> para, int intPageIndex, int intPageSize)
        {
            StringBuilder strQuery = new StringBuilder();
            strQuery.AppendLine("SELECT di.containerid,c.containername,mo.processno,mo.oprno,pb.productname,");
            strQuery.AppendLine("       p.description,mo.mfgordername,c.qty,c.originalstartdate,NVL(c.state,0) AS state,");
            strQuery.AppendLine("       c.plannedcompletiondate AS contcompdate,mo.plannedcompletiondate AS ordercompdate,");
            strQuery.AppendLine("       MAX(di.plannedcompletiondate) AS realcompdate,mo.mfgmanagerid,wfs.workflowid,c.productid,c.containercomments,e.fullname MfgManager");
            strQuery.AppendLine("FROM dispatchinfo di");
            strQuery.AppendLine("LEFT JOIN container c ON c.containerid = di.containerid");
            strQuery.AppendLine("LEFT JOIN product p ON p.productid = c.productid");
            strQuery.AppendLine("LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid");
            strQuery.AppendLine("LEFT JOIN mfgorder mo ON mo.mfgorderid = c.mfgorderid");
            strQuery.AppendLine("LEFT JOIN spec s ON s.specid = di.specid");
            strQuery.AppendLine("LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid");
            strQuery.AppendLine("LEFT JOIN currentstatus cu ON cu.currentstatusid = c.currentstatusid");
            strQuery.AppendLine("LEFT JOIN workflowstep wfs ON wfs.workflowstepid = cu.workflowstepid");
            strQuery.AppendLine("left join employee e on e.employeeid=di.dispatchemployeeid");
            strQuery.AppendLine("LEFT JOIN containerspecfinishinfo csfi ON csfi.containerid = di.containerid AND csfi.specid = di.specid AND csfi.workflowid = di.workflowid");
            strQuery.AppendLine("WHERE 1 = 1 AND di.isaps = 1 AND di.dispatchtype = 0 AND dispatchtotype = 0");
            strQuery.AppendLine("AND c.status = 1 AND c.parentcontainerid IS NULL AND c.containername <> c.containerid");
            strQuery.AppendLine("AND csfi.containerid IS NULL");
            strQuery.AppendLine("AND di.plannedstartdate IS NOT NULL AND di.plannedcompletiondate IS NOT NULL");

            if (para.Keys.Contains("ProductID")&& !string.IsNullOrEmpty(para["ProductID"]))//add;Wangjh
            {
                strQuery.AppendFormat(" and p.productid='{0}' ",para["ProductID"]);
            }

            if (para.Keys.Contains("MfgManagerID") && !string.IsNullOrEmpty(para["MfgManagerID"]))//add;Wangjh
            {
                strQuery.AppendFormat(" and e.employeeid='{0}' ", para["MfgManagerID"]);
            }

            if (para.Keys.Contains("ProductName2") && !string.IsNullOrEmpty(para["ProductName2"]))//add;Wangjh
            {
                strQuery.AppendFormat(" and pb.productname='{0}' ", para["ProductName2"]);
            }

            if (para.Keys.Contains("ScanContainerName")) //扫描的批次号
            {
                if (!string.IsNullOrEmpty(para["ScanContainerName"]))
                {
                    strQuery.AppendLine(string.Format("AND c.containername = '{0}'", para["ScanContainerName"]));
                }
            }
            if (para.Keys.Contains("ProcessNo")) //工作令号
            {
                if (!string.IsNullOrEmpty(para["ProcessNo"]))
                {
                    strQuery.AppendLine(string.Format("AND LOWER(mo.processno) LIKE '%{0}%'", para["ProcessNo"].ToLower()));
                }
            }
            if (para.Keys.Contains("ContainerName")) //批次号
            {
                if (!string.IsNullOrEmpty(para["ContainerName"]))
                {
                    strQuery.AppendLine(string.Format("AND LOWER(c.containername) LIKE '%{0}%'", para["ContainerName"].ToLower()));
                }
            }
            if (para.Keys.Contains("ProductName")) //图号/名称
            {
                if (!string.IsNullOrEmpty(para["ProductName"]))
                {
                    strQuery.AppendLine(string.Format("AND (LOWER(pb.productname) LIKE '%{0}%' OR LOWER(p.description) LIKE '%{0}%')", para["ProductName"].ToLower()));
                }
            }
            if (para.Keys.Contains("SpecName")) //工序
            {
                if (!string.IsNullOrEmpty(para["SpecName"]))
                {
                    strQuery.AppendLine(string.Format("AND LOWER(sb.specname) LIKE '%{0}%'", para["SpecName"].ToLower()));
                }
            }
            if (para.Keys.Contains("StartDate")) //开始时间
            {
                if (!string.IsNullOrEmpty(para["StartDate"]))
                {
                    strQuery.AppendLine(string.Format("AND di.plannedstartdate >= TO_DATE('{0} 00:00:00','yyyy-MM-dd HH24:MI:SS')", para["StartDate"]));
                }
            }
            if (para.Keys.Contains("EndDate")) //结束时间
            {
                if (!string.IsNullOrEmpty(para["EndDate"]))
                {
                    strQuery.AppendLine(string.Format("AND di.plannedstartdate  <= TO_DATE('{0} 23:59:59','yyyy-MM-dd HH24:MI:SS')", para["EndDate"]));
                }
            }
            if (para.Keys.Contains("State"))
            {
                if (!string.IsNullOrEmpty(para["State"]))
                {
                    strQuery.AppendLine(string.Format("AND c.state = {0}", para["State"]));
                }
            }

            strQuery.AppendLine("GROUP BY di.containerid,c.containername,mo.processno,mo.oprno,pb.productname,");
            strQuery.AppendLine("   p.description,mo.mfgordername,c.qty,c.originalstartdate,c.state,");
            strQuery.AppendLine("   c.plannedcompletiondate,mo.plannedcompletiondate,mo.mfgmanagerid,wfs.workflowid,c.productid,c.containercomments,e.fullname");
            strQuery.AppendLine("ORDER BY pb.productname ASC");

            uMESPagingDataDTO result = OracleHelper.GetPagingDataIns(strQuery.ToString(), intPageIndex, intPageSize);

            DataTable dtMain = result.DBTable.Copy();

            //跟踪卡主信息
            dtMain.TableName = "ContainerName";
            result.DBset.Tables.Add(dtMain);

            //跟踪卡ID
            string strConIDList = "'',";
            if (dtMain.Rows.Count > 0)
            {
                for (int i = 0; i < dtMain.Rows.Count; i++)
                {
                    strConIDList += "'" + dtMain.Rows[i]["ContainerID"].ToString() + "',";
                }
            }
            strConIDList = strConIDList.TrimEnd(',');

            //查询详细信息
            string strDayGS = ConfigurationManager.AppSettings["DayGS"];
            //string strDays = "0";// para["Days"].ToString();
            string strDays = "0";
            if (para.Keys.Contains("Days"))
            {
                if (!string.IsNullOrEmpty(para["Days"]))
                {
                    strDays = para["Days"].ToString();
                }
            }
            strQuery = new StringBuilder();
            strQuery.AppendLine("SELECT NVL(SUM(di.totalgs), 0) AS totalgs, di.resourcegroupid,");
            strQuery.AppendLine("((SELECT COUNT(*) FROM resourcegroupentries re WHERE re.resourcegroupid = di.resourcegroupid) * " + strDayGS + " * 60 * " + strDays + ") AS stdgs,");
            strQuery.AppendLine("((SELECT COUNT(*) FROM resourcegroupentries re WHERE re.resourcegroupid = di.resourcegroupid) * " + strDayGS + " * 60) AS daygs");
            strQuery.AppendLine("FROM dispatchinfo di");
            strQuery.AppendLine("LEFT JOIN synergicinfo sy ON sy.containerid = di.containerid");
            strQuery.AppendLine("WHERE 1 = 1");
            strQuery.AppendLine("AND di.isaps = 1 AND di.dispatchtype = 0 AND dispatchtotype = 0");
            strQuery.AppendLine("AND di.resourcegroupid IS NOT NULL");
            strQuery.AppendLine("AND (di.specsequence < sy.sequence OR di.specsequence > sy.returnsequence OR sy.sequence IS NULL)");

            if (para.Keys.Contains("StartDate")) //开始时间
            {
                if (!string.IsNullOrEmpty(para["StartDate"]))
                {
                    strQuery.AppendLine(string.Format("AND di.plannedstartdate >= TO_DATE('{0} 00:00:00','yyyy-MM-dd HH24:MI:SS')", para["StartDate"]));
                }
            }

            if (para.Keys.Contains("EndDate")) //结束时间
            {
                if (!string.IsNullOrEmpty(para["EndDate"]))
                {
                    strQuery.AppendLine(string.Format("AND di.plannedstartdate  <= TO_DATE('{0} 23:59:59','yyyy-MM-dd HH24:MI:SS')", para["EndDate"]));
                }
            }

            strQuery.AppendLine("GROUP BY di.resourcegroupid");

            DataTable dtRGGS = OracleHelper.GetDataTable(strQuery.ToString());

            dtRGGS.Columns.Add("Status");
            dtRGGS.Columns["Status"].DefaultValue = "0";

            foreach (DataRow row in dtRGGS.Rows)
            {
                Double totalgs = Convert.ToDouble(row["TotalGS"]);
                Double stdgs = Convert.ToDouble(row["StdGS"]);

                if (totalgs > stdgs)
                {
                    row["Status"] = "1";
                }
            }

            strQuery = new StringBuilder();
            strQuery.AppendLine("SELECT di.id,di.dispatchinfoname,di.containerid,sb.specname,di.totalgs,di.resourcegroupid,di.specsequence,rg.resourcegroupname");
            strQuery.AppendLine("   ,di.plannedstartdate,di.plannedcompletiondate,");
            strQuery.AppendLine("   mo.processno,mo.oprno,c.containername,pb.productname,p.description,");
            strQuery.AppendLine("   (CASE WHEN di.specsequence >= sy.sequence AND di.specsequence <= sy.returnsequence THEN '1' ELSE '0' END) AS iswx");
            //strQuery.AppendLine("       ,(SELECT COUNT(*) FROM resourcegroupentries re WHERE re.resourcegroupid = di.resourcegroupid) AS resqty");
            strQuery.AppendLine("FROM dispatchinfo di");
            strQuery.AppendLine("LEFT JOIN container c ON c.containerid = di.containerid");
            strQuery.AppendLine("LEFT JOIN product p ON p.productid = c.productid");
            strQuery.AppendLine("LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid");
            strQuery.AppendLine("LEFT JOIN mfgorder mo ON mo.mfgorderid = c.mfgorderid");
            strQuery.AppendLine("LEFT JOIN spec s ON s.specid = di.specid");
            strQuery.AppendLine("LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid");
            strQuery.AppendLine("LEFT JOIN resourcegroup rg ON rg.resourcegroupid = di.resourcegroupid");
            strQuery.AppendLine("LEFT JOIN synergicinfo sy ON sy.containerid = di.containerid");
            strQuery.AppendLine("WHERE di.isaps = 1 AND di.dispatchtype = 0 AND dispatchtotype = 0");
            strQuery.AppendLine("AND di.containerid IN (" + strConIDList + ")");

            if (para.Keys.Contains("StartDate")) //开始时间
            {
                if (!string.IsNullOrEmpty(para["StartDate"]))
                {
                    strQuery.AppendLine(string.Format("AND di.plannedstartdate >= TO_DATE('{0} 00:00:00','yyyy-MM-dd HH24:MI:SS')", para["StartDate"]));
                }
            }
            if (para.Keys.Contains("EndDate")) //结束时间
            {
                if (!string.IsNullOrEmpty(para["EndDate"]))
                {
                    strQuery.AppendLine(string.Format("AND di.plannedstartdate  <= TO_DATE('{0} 23:59:59','yyyy-MM-dd HH24:MI:SS')", para["EndDate"]));
                }
            }

            DataTable dtDetail = OracleHelper.GetDataTable(strQuery.ToString());
            
            dtDetail.Columns.Add("state");
            dtDetail.Columns["state"].DefaultValue = "0";

            //foreach (DataRow row in dtDetail.Rows)
            //{
            //    string strRGID = row["ResourceGroupID"].ToString();

            //    DataRow[] rows = dtRGGS.Select("ResourceGroupID = '" + strRGID + "'");

            //    if (rows.Length > 0)
            //    {
            //        string strStatus = rows[0]["Status"].ToString();
            //        row["state"] = strStatus;
            //    }
            //}

            dtDetail.TableName = "DetailInfo";
            result.DBset.Tables.Add(dtDetail);

            dtRGGS.TableName = "RGGS";
            result.DBset.Tables.Add(dtRGGS);

            return result;
        }
        #endregion

        #region 是否全天24小时工作
        public Boolean Is24H()
        {
            string strDayGS = ConfigurationManager.AppSettings["DayGS"].ToString();

            if (strDayGS == "24")
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        #endregion

        #region 是否包含周末
        public Boolean IsIncludeSS()
        {
            string strConfig = ConfigurationManager.AppSettings["IsIncludeSS"].ToString();

            if (strConfig.ToLower() == "true")
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        #endregion

        #region 获取每日工作时间段
        public DataTable GetDaySJD_Config()
        {
            DataTable DT = new DataTable();
            DT.Columns.Add("start");
            DT.Columns.Add("end");

            string strConfig = ConfigurationManager.AppSettings["DaySJD"].ToString();
            string[] arrayConfig = strConfig.Split(',');

            foreach (string str in arrayConfig)
            {
                string[] arrayS = str.Split('-');

                DataRow row = DT.NewRow();
                row["start"] = arrayS[0];
                row["end"] = arrayS[1];
                DT.Rows.Add(row);
            }

            return DT;
        }
        #endregion

        #region 根据给定的开始时间、工时、每日工作时间段计算结束时间
        /// <summary>
        /// 根据给定的开始时间、工时计算开始时间和结束时间
        /// </summary>
        /// <param name="startDateTime"></param>
        /// <param name="dblGS"></param>
        /// <returns></returns>
        public PlannedTimes GetPlannedTimes(DateTime startDateTime, Double dblGS)
        {
            PlannedTimes pt = new PlannedTimes();

            DateTime startTime = new DateTime();
            DateTime endTime = new DateTime();
            Boolean is24H = Is24H();

            if (is24H == true)
            {
                startTime = startDateTime;
                endTime = startTime.AddMinutes(dblGS);
            }
            else
            {
                DateTime startTimeTemp = new DateTime();
                DataTable dtDaySJD = GetDaySJD_Config();
                Boolean isIncludeSS = IsIncludeSS();

                GetEndDateTime(startDateTime, out startTime, out endTime, dblGS, dtDaySJD, 0, startTimeTemp, isIncludeSS, false);
            }

            pt.StartTime = startTime;
            pt.EndTime = endTime;

            return pt;
        }

        /// <summary>
        /// 根据给定的开始时间、工时、每日工作时间段计算结束时间
        /// </summary>
        /// <param name="startDateTime">给定的开始时间</param>
        /// <param name="startTime">计算出的开始时间，只需给定任意初始值</param>
        /// <param name="endTime">计算出的结束时间</param>
        /// <param name="dblGS">工时（单位：分钟）</param>
        /// <param name="DT">每日工作时间段</param>
        /// <param name="intS">内部参数：固定为 0 </param>
        /// <param name="startTimeTemp">内部参数，只需给定任意初始值</param>
        /// <param name="boolIncludeSS">是否包含周末（周六和周日）</param>
        /// <param name="noStartTime">是否已产生开始时间</param>
        protected void GetEndDateTime(DateTime startDateTime, out DateTime startTime, out DateTime endTime,
            Double dblGS, DataTable DT, int intS, DateTime startTimeTemp, Boolean boolIncludeSS, Boolean noStartTime)
        {
            startDateTime = GetStartDateTime(startDateTime, boolIncludeSS);

            if (intS == 0) //第一次执行时，输出的开始时间设定为给定的开始时间
            {
                startTime = startDateTime;
                startTimeTemp = startTime;
            }
            else //递归时记录输出的开始时间
            {
                startTime = startTimeTemp;
            }

            endTime = DateTime.Now;

            DataTable dt = GetDaySJD(startDateTime, DT); //获取给定日期的工作时间段

            if (intS == 1) //递归时，开始时间为最早工作时间段的开始时间
            {
                startDateTime = Convert.ToDateTime(dt.Rows[0]["StartTime"]);
                if (noStartTime == true)
                {
                    startTime = startDateTime;
                    startTimeTemp = startDateTime;

                    noStartTime = false;
                }
            }

            //获取开始时间所在及之后的工作时间段
            DataRow[] rows = dt.Select(string.Format("(StartTime <= #{0}# AND EndTime > #{0}#) OR (StartTime >= #{0}#)", startDateTime), "StartTime ASC");

            Double dblGS2 = dblGS;
            //按顺序将工时排入工作时间段，同时处理开始时间和结束时间
            for (int i = 0; i < rows.Length; i++)
            {
                DateTime sTime = Convert.ToDateTime(rows[i]["StartTime"]);
                if (i == 0 && intS == 0)
                {
                    if (startDateTime >= sTime)
                    {
                        sTime = startDateTime;
                    }
                    else
                    {
                        startTime = sTime;
                        startTimeTemp = startTime;
                    }
                }

                DateTime eTime = Convert.ToDateTime(rows[i]["EndTime"]);
                Double T1 = GetM(sTime, eTime);

                if (T1 >= dblGS)
                {
                    endTime = sTime.AddMinutes(dblGS);
                    dblGS = 0;
                    break;
                }
                else
                {
                    dblGS = dblGS - T1;
                }
            }

            //当天全部时间段全部拍完后，如果工时仍大于零，则向第二天继续排
            if (dblGS > 0)
            {
                if (dblGS == dblGS2)
                {
                    noStartTime = true;
                }

                startDateTime = startDateTime.AddDays(1);
                intS = 1;

                GetEndDateTime(startDateTime, out startTime, out endTime, dblGS, DT, 1, startTimeTemp, boolIncludeSS, noStartTime);
            }
        }

        protected DateTime GetStartDateTime(DateTime date, Boolean boolIncludeSS)
        {
            DateTime result = date;

            if (boolIncludeSS == false)
            {
                if (date.DayOfWeek == DayOfWeek.Saturday)
                {
                    result = date.AddDays(2);
                }

                if (date.DayOfWeek == DayOfWeek.Sunday)
                {
                    result = date.AddDays(1);
                }
            }

            return result;
        }

        /// <summary>
        /// 获取给定日期的工作时间段
        /// </summary>
        /// <param name="date"></param>
        /// <param name="DT"></param>
        /// <returns></returns>
        protected DataTable GetDaySJD(DateTime date, DataTable DT)
        {
            DataTable dt = DT.Copy();

            dt.Columns.Add("StartTime", System.Type.GetType("System.DateTime"));
            dt.Columns.Add("EndTime", System.Type.GetType("System.DateTime"));

            foreach (DataRow row in dt.Rows)
            {
                string strDate = date.ToString("yyyy-MM-dd");
                string strStartTime = row["start"].ToString();
                string strEndTime = row["end"].ToString();

                row["StartTime"] = Convert.ToDateTime(string.Format("{0} {1}", strDate, strStartTime));
                row["EndTime"] = Convert.ToDateTime(string.Format("{0} {1}", strDate, strEndTime));
            }

            return dt;
        }

        /// <summary>
        /// 计算两个时间点之间的时间差（单位：分钟）
        /// </summary>
        /// <param name="dt1"></param>
        /// <param name="dt2"></param>
        /// <returns></returns>
        protected Double GetM(DateTime dt1, DateTime dt2)
        {
            TimeSpan ts = dt1.Subtract(dt2);

            Double intD = ts.Days;
            Double intH = ts.Hours;
            Double intM = ts.Minutes;

            return Math.Abs(intD * 24 * 60 + intH * 60 + intM);
        }
        #endregion
    }

    public class PlannedTimes
    {
        public DateTime StartTime { get; set; }
        public DateTime EndTime { get; set; }
    }
}
